package com.metacube.questionbank.dao;

import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.metacube.questionbank.jdbc.QuestionRowMapper;
import com.metacube.questionbank.jdbc.TagRowMapper;
import com.metacube.questionbank.model.Question;
import com.metacube.questionbank.model.Tag;

/*
 * @author Team Neeraj Jain
 * */
@Repository
public class TagDAO {
	@Autowired
	DataSource dataSource;

	/*
	 * This method returns all the tags that are in the database.
	 * 
	 * @return list of tags
	 */
	public List<Tag> getTags() {
		List<Tag> tagList = new ArrayList<Tag>();
		String sql = "select t.tag_id,t.tag_name,t.tag_desc,count(tq.ques_id) as noOfQues from tags t LEFT JOIN Questiontags tq on t.tag_id=tq.tag_id  group by t.tag_id;";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		tagList = jdbcTemplate.query(sql, new TagRowMapper());
		return tagList;
	}

	/*
	 * @param tagId id of tag This method returns a list of questions that are
	 * related to a particular tag
	 * 
	 * @return list of questions.
	 */
	public List<Question> getTagQuestions(int tag_id) {
		List<Question> tagQuestionsList = new ArrayList<Question>();
		String sql = "select q.ques_id,q.ques_title,q.ques_desc,q.user_id,q.ques_date,q.closing_date from questions q,Questiontags tq where q.ques_id=tq.ques_id and tq.tag_id='"
				+ tag_id + "'";

		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		tagQuestionsList = jdbcTemplate.query(sql, new QuestionRowMapper());
		return tagQuestionsList;
	}

	/*
	 * @param tagName name of tag This method returns a list of tags according
	 * to the string that is typed by user in the search bar of tag page.
	 * 
	 * @return list of tag
	 */
	public List<Tag> getMatchedTag(String tagName) {
		List<Tag> tagList = new ArrayList<Tag>();
		String sql = "select t.tag_id,t.tag_name,t.tag_desc,count(tq.ques_id) as noOfQues from tags t LEFT JOIN Questiontags tq on t.tag_id=tq.tag_id  group by t.tag_id having t.tag_name like '"
				+ tagName + "%';";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		tagList = jdbcTemplate.query(sql, new TagRowMapper());
		return tagList;
	}

	/* no need of this method */
	public void insertTag(int tag_id, int ques_id) {
		String sql = "INSERT INTO questions (tag_id,ques_id) VALUES (?,?)";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] { tag_id, ques_id });
	}

	/*
	 * @param quesId id of question This method returns a list of tags as per
	 * the id of question
	 * 
	 * @return list of tag
	 */
	public List<Tag> getQuestionTags(int quesId) {
		List<Tag> questionTagsList = new ArrayList<Tag>();
		String sql = "select t.tag_id,t.tag_name,t.tag_desc,count(tq.ques_id) as noOfQues from Questiontags tq LEFT JOIN tags t on t.tag_id=tq.tag_id where tq.ques_id='"
				+ quesId + "' group by t.tag_id";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		questionTagsList = jdbcTemplate.query(sql, new TagRowMapper());
		return questionTagsList;
	}

	/*
	 * @param objTag object of Tag class This method inserts a tag in the
	 * database
	 */
	public void insertTag(Tag tag) {
		String sql = "INSERT INTO tags (tagName,tagDesc) VALUES (?,?)";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql,
				new Object[] { tag.getTagName(), tag.getTagDesc() });
	}

	/*
	 * @param tagName name of tag This method returns the object of tag where
	 * tag name is same as the tag name passed in argument
	 * 
	 * @return tag object of tag class.
	 */
	public Tag getTag(String tagName) {
		Tag tag = null;
		String sql = "select tag_id,tag_name,tag_desc,0 as noOfQues from tags  where tag_name='"
				+ tagName + "'";
		try {
			JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
			tag = jdbcTemplate.queryForObject(sql, new TagRowMapper());
		} catch (Exception exp) {
			
		}
		return tag;
	}

	/*
	 * @param tagId id of tag
	 * 
	 * @param quesId id of question This method associates tags with the
	 * question.
	 */
	public void insertQuestionTags(int tagId, int quesId) {
		String sql = "INSERT INTO questionTags (tag_id,ques_id) VALUES (?,?)";
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
		jdbcTemplate.update(sql, new Object[] { tagId, quesId });
	}
}